﻿//
// (C) Copyright 2009 Irantha Suwandarathna (irantha@gmail.com)
// All rights reserved.
//

using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using EffiProzDB;
using EffiProzDB.Lib;
using EffiProzDB.DataTypes;
using EffiProzDB.Persist;

namespace EffiProzDBTest
{
    /// <summary>
    /// Summary description for TriggerTest
    /// </summary>
    [TestClass]
    public class TriggerTest : BaseTest
    {
        public TriggerTest()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        private TestContext testContextInstance;

        /// <summary>
        ///Gets or sets the test context which provides
        ///information about and functionality for the current test run.
        ///</summary>
        public TestContext TestContext
        {
            get
            {
                return testContextInstance;
            }
            set
            {
                testContextInstance = value;
            }
        }

        #region Additional test attributes
        //
        // You can use the following additional attributes as you write your tests:
        //
        // Use ClassInitialize to run code before running the first test in the class
        // [ClassInitialize()]
        // public static void MyClassInitialize(TestContext testContext) { }
        //
        // Use ClassCleanup to run code after all tests in a class have run
        // [ClassCleanup()]
        // public static void MyClassCleanup() { }
        //
        // Use TestInitialize to run code before running each test 
        // [TestInitialize()]
        // public void MyTestInitialize() { }
        //
        // Use TestCleanup to run code after each test has run
        // [TestCleanup()]
        // public void MyTestCleanup() { }
        //
        #endregion

        [TestMethod]
        public void LockFileTest()
        {
            CleanUp("LockFileTest");
            HsqlProperties props2 = new HsqlProperties();
            props2.setProperty(HsqlDatabaseProperties.hsqldb_lock_file,true);
            SessionInterface sessionProxy = DatabaseManager.newSession(DatabaseURL.S_FILE, dataPath + "LockFileTest",
                     user, password, props2);

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE CACHED  TABLE " + table + " (col1 INT , col2 VARCHAR); ";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            int col1val = 8;
            string col2val = "Hoo";
            sql = TestHelper.CreateStdInsert(table,2,col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val);


            LockFile lockfile = null;
            try
            {
                lockfile = LockFile.newLockFileLock(dataPath + "LockFileTest");
                Assert.Fail("Lock File Error");
            }
            catch (Exception) { }


            sql = "SHUTDOWN ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            try
            {
                lockfile = LockFile.newLockFileLock(dataPath + "LockFileTest");
                
            }
            catch (Exception) {
                Assert.Fail("Lock File Error");
            }

            Assert.IsTrue(lockfile.isLocked());

            sessionProxy.close();
        }



        [TestMethod]
        public void StoredProcedures()
        {
            CleanUp("SPTest");
            SessionInterface sessionProxy = DatabaseManager.newSession(DatabaseURL.S_FILE, dataPath + "SPTest",
                     user, password, props);

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE CACHED  TABLE " + table + " (col1 INT , col2 VARCHAR); ";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            sql = "CREATE ALIAS AddThree For \"C:\\Users\\irantha\\Documents\\Visual Studio 10\\Projects\\EffiProz\\EffiProzDBTest\\bin\\Debug\\EffiProzDBTest.dll#" + typeof(SampleTrigger).FullName + ".AddThree\";";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = "CREATE ALIAS SQRTTest FOR \"System.Math.Sqrt\";";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 8;
            string col2val = "Hoo";
            sql = "INSERT INTO " + table + "(col1,col2) VALUES(AddThree(5), '" + col2val + "');";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val);

            sql = "CALL AddThree(5) ;";
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val);

            sql = "CREATE ALIAS AddThree FOR \"System.Math.Sqrt\";";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            double dcol1val = Math.Sqrt(5);
            sql = "CALL AddThree(5) ;";
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, dcol1val);

        }



        [TestMethod]
        public void Triggers()
        {
            CleanUp("TriggerTest");
            SessionInterface sessionProxy = DatabaseManager.newSession(DatabaseURL.S_FILE, dataPath + "TriggerTest",
                     user, password, props);

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE CACHED  TABLE " + table + " (col1 INT , col2 VARCHAR); ";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = "CREATE TRIGGER TriggerBIR BEFORE INSERT ON " + table + " FOR EACH ROW QUEUE 0 CALL \"C:\\Users\\irantha\\Documents\\Visual Studio 10\\Projects\\EffiProz\\EffiProzDBTest\\bin\\Debug\\EffiProzDBTest.dll#"
                + typeof(SampleTrigger).FullName + "\";";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = "CREATE TRIGGER TriggerAIR BEFORE INSERT ON " + table + " FOR EACH ROW QUEUE 0 CALL \"C:\\Users\\irantha\\Documents\\Visual Studio 10\\Projects\\EffiProz\\EffiProzDBTest\\bin\\Debug\\EffiProzDBTest.dll#"
               + typeof(SampleTrigger).FullName + "\";";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 180;
            string col2val = "Hoo";
            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            col1val = 100;
            TestHelper.MatchSingleResultRow(resultIn,0,  col1val, col2val);

            sql = "CREATE TRIGGER TriggerBUR BEFORE UPDATE ON " + table + " FOR EACH ROW QUEUE 0 CALL \"C:\\Users\\irantha\\Documents\\Visual Studio 10\\Projects\\EffiProz\\EffiProzDBTest\\bin\\Debug\\EffiProzDBTest.dll#"
               + typeof(SampleTrigger).FullName + "\";";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = "CREATE TRIGGER TriggerAUR AFTER UPDATE ON " + table + " FOR EACH ROW QUEUE 0 CALL \"C:\\Users\\irantha\\Documents\\Visual Studio 10\\Projects\\EffiProz\\EffiProzDBTest\\bin\\Debug\\EffiProzDBTest.dll#"
               + typeof(SampleTrigger).FullName + "\";";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdUpdate(table, "col2", "nochange");
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            col1val = 100;
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val);

            sql = "CREATE TRIGGER TriggerBDR BEFORE DELETE ON " + table + " FOR EACH ROW QUEUE 0 CALL \"C:\\Users\\irantha\\Documents\\Visual Studio 10\\Projects\\EffiProz\\EffiProzDBTest\\bin\\Debug\\EffiProzDBTest.dll#"
              + typeof(SampleTrigger).FullName + "\";";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = "CREATE TRIGGER TriggerADR AFTER DELETE ON " + table + " FOR EACH ROW QUEUE 0 CALL \"C:\\Users\\irantha\\Documents\\Visual Studio 10\\Projects\\EffiProz\\EffiProzDBTest\\bin\\Debug\\EffiProzDBTest.dll#"
               + typeof(SampleTrigger).FullName + "\";";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = "DELETE FROM " + table + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = "DROP TRIGGER  TriggerBDR; DROP TRIGGER  TriggerADR;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

        }
    }


    public class SampleTrigger : Trigger
    {

        public static bool UpdateBeforeFired = false;
        public static bool UpdateAfterFired = false;
        public static bool InsertBeforeFired = false;
        public static bool InsertAfterFired = false;
        public static bool DeleteBeforeFired = false;
        public static bool DeleteAfterFired = false;

        public static bool Error = false;

        public static int AddThree(int x)
        {
            return x + 3;
        }

        public void fire(int typ, String trn, String tn, Object[] or,
                    Object[] nr)
        {

            lock (typeof(SampleTrigger))
            {

                switch (typ)
                {

                    case TriggerConstants.INSERT_BEFORE_ROW:
                        int val = (int)nr[0];
                        if (val > 100)
                            nr[0] = 100;

                        InsertBeforeFired = true;
                        break;

                    case TriggerConstants.INSERT_AFTER_ROW:

                        if (!InsertBeforeFired)
                            System.Diagnostics.Debug.Fail("Trigger Before Insert not Fired");

                        InsertAfterFired = true;
                        break;

                    case TriggerConstants.UPDATE_BEFORE_ROW:
                        string sval = (string)nr[1];

                        if (sval == "nochange")
                            nr[1] = or[1];

                        UpdateBeforeFired = true;
                        break;

                    case TriggerConstants.UPDATE_AFTER_ROW:

                        if (!UpdateBeforeFired)
                            System.Diagnostics.Debug.Fail("Trigger Before Update not Fired");

                        UpdateAfterFired = true;
                        break;

                    case TriggerConstants.DELETE_BEFORE_ROW:

                      
                        DeleteBeforeFired = true;
                        break;

                    case TriggerConstants.DELETE_AFTER_ROW:

                        if (!UpdateBeforeFired)
                            System.Diagnostics.Debug.Fail("Trigger Before Delete not Fired");

                        UpdateAfterFired = true;
                        break;

                }
            }

        }
    }
}
